package com.rockcent.mall.lib.repository;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.rockcent.common.common.PageResult;
import com.rockcent.common.common.SQLListDoing;
import com.rockcent.common.common.SQLObjectDoing;
import com.rockcent.common.common.SQLPageDoing;
import com.rockcent.common.domain.enumclass.Whether;
import com.rockcent.common.utils.RDateUtils;
import com.rockcent.mall.lib.domain.MerchantOrder;
import com.rockcent.mall.lib.domain.enumclass.*;
import com.rockcent.mall.lib.dto.ChannelProductDto;
import com.rockcent.mall.lib.dto.MerchantOrderDto;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Arrays;
import java.util.List;

/**
 * Created by DK on 16/10/9.
 */
@Repository
public class MerchantOrderRepositoryImpl<T> implements MerchantOrderRepositoryCustom<T> {

    private static final Logger log = LoggerFactory.getLogger(MerchantOrderRepositoryImpl.class);

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    @Autowired
    private ObjectMapper objectMapper;

    @Override
    public PageResult<MerchantOrderDto> findMerchantOrderPage(Long merchantId, MerchantOrderDto dto, PageResult pageResult) {
        SQLPageDoing<MerchantOrderDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM merchant_order mo WHERE 1=1");

            if (StringUtils.isNotBlank(dto.getOrderNum())) {
                sql.append(" AND mo.order_num LIKE :orderNum");
                params.put("orderNum", "%" + dto.getOrderNum() + "%");
            }

            if (StringUtils.isNotBlank(dto.getCustomerName())) {
                sql.append(" AND mo.customer_name LIKE :customerName");
                params.put("customerName", "%" + dto.getCustomerName() + "%");
            }

            if (OrderType.ALL != OrderType.valueOf(dto.getOrderType())) {
                sql.append(" AND mo.order_type = :orderType");
                params.put("orderType", dto.getOrderType());
            }

            if (StringUtils.isNotBlank(dto.getSourceType())) {
                sql.append(" AND mo.source_type = :sourceType");
                params.put("sourceType", dto.getSourceType());
            }

            if (ProductShippingType.ALL != ProductShippingType.valueOf(dto.getProductShoppingType())) {
                sql.append(" AND mo.product_shopping_type LIKE :productShoppingType");
                params.put("productShoppingType", "%" + dto.getProductShoppingType() + "%");
            }

            if (StringUtils.isNotBlank(dto.getShoppingManPhone())) {
                sql.append(" AND (mo.shopping_man LIKE :shoppingManPhone OR mo.shopping_man_phone LIKE :shoppingManPhone )");
                params.put("shoppingManPhone", "%" + dto.getShoppingManPhone() + "%");
            }

            if (OrderStatus.ALL != OrderStatus.valueOf(dto.getStatus())) {
                if (OrderStatus.FINISHED == OrderStatus.valueOf(dto.getStatus())) {
                    sql.append(" AND mo.status in (:status) ");
                    params.put("status", Arrays.asList(OrderStatus.FINISHED.toString(), OrderStatus.CANCEL.toString()));
                } else if (OrderStatus.WAIT_SHIPPED == OrderStatus.valueOf(dto.getStatus())) {
                    sql.append(" AND mo.status in (:status) ");
                    params.put("status", Arrays.asList(OrderStatus.WAIT_SHIPPED.toString(), OrderStatus.WAIT_STOCK.toString()));
                } else {
                    sql.append(" AND mo.status = :status");
                    params.put("status", dto.getStatus());
                }
            }

            sql.append(" AND mo.date_created < :endDate AND mo.date_created > :beginDate");
            params.put("beginDate", RDateUtils.beforeDateStr(dto.getBeginDate(), 0));
            params.put("endDate", RDateUtils.afterDateStr(dto.getEndDate(), 1));
            sql.append(" AND mo.merchant_id = :merchantId");
            params.put("merchantId", merchantId);

            sql.append(" ORDER BY mo.date_created desc");
            fields.append("mo.*");
        };

        return doing.go(pageResult, MerchantOrderDto.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public Long countProductSaleByChannelId(Long productId, Long merchantId, Long mallId, Long channelId) {

        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from merchant_order_item moi");
            sql.append(" left join merchant_order o on o.id=moi.merchant_order_id");
            sql.append(" where  moi.merchant_id=:merchantId");
            sql.append(" and o.status in(:status)");
            sql.append(" and moi.mall_id=:mallId and moi.is_delete='NO'");
            sql.append(" and moi.channel_id=:channelId and moi.product_id=:productId");

            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            params.put("channelId", channelId);
            params.put("status", Arrays.asList(OrderStatus.FINISHED, OrderStatus.CONFIRM));
            params.put("productId", productId);
            fields.append("CASE WHEN  SUM(moi.buy_count) IS NULL THEN 0 ELSE SUM(moi.buy_count) END");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public Long countProductVerifyByChannelId(Long productId, Long merchantId, Long mallId, Long channelId) {

        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from merchant_order_verification mov");
            sql.append(" left join merchant_order_item mo on mo.id=mov.merchant_order_item_id  where mov.is_delete='NO'");
            sql.append(" and mo.channel_id=:channelId and mov.product_id=:productId");
            sql.append(" and mov.merchant_id=:merchantId and mov.mall_id=:mallId");
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            params.put("channelId", channelId);
            params.put("productId", productId);

            fields.append("CASE WHEN  SUM(mov.verify_count) IS NULL THEN 0 ELSE SUM(mov.verify_count) END");

        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }


    /**
     * 统计每个渠道下单人数
     *
     * @param channelId
     * @param mallId
     * @param merchantId
     * @param type
     * @param typeId
     * @param orderSourceType
     * @param startDate
     * @param endDate         @return
     */
    @Override
    public Long countMerchantOrderByChannelIdAndMallIdAndMerchantId(Long channelId, Long mallId, Long merchantId, String type, Long typeId, MerchantOrderSourceType orderSourceType, String startDate, String endDate) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            /**
             *
             * SELECT COUNT(mo.`customer_id`) FROM merchant_order_item moi
             LEFT JOIN merchant_order mo ON mo.`id`=moi.`merchant_order_id`
             WHERE moi.`merchant_id`=1
             AND moi.`mall_id`=1
             AND moi.`product_id`=1
             AND mo.`date_created` >='2016-10-01'
             AND mo.`date_created`<='2016-10-01'
             AND mo.`merchant_order_ext_source_type`='source'
             AND mo.`merchant_order_ext_type`='SELF'
             GROUP BY mo.`customer_id`;
             */
            sql.append("select %s from merchant_order_verification mov");
            sql.append("  left join merchant_order_item mo on mo.id=mov.merchant_order_item_id");
            sql.append("  LEFT JOIN product p ON p.`id`=mo.product_id");
            sql.append("  where mov.is_delete='NO'");
            sql.append("  and mo.channel_id=:channelId");
            sql.append("  and mov.merchant_id=:merchantId and mov.mall_id=:mallId");

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND  p.type=:type");
                params.put("type", type);
            }

            if (typeId != null) {
                sql.append(" AND  c.channel_type_id=:typeId");
                params.put("typeId", typeId);
            }


            buildMakertIdSql(orderSourceType, sql);

            if (startDate != null) {
                sql.append(" AND mo.`date_created` >=:startDate");
                params.put("startDate", startDate);
            }
            if (endDate != null) {
                sql.append(" AND mo.`date_created` <=:endDate");
                params.put("endDate", endDate);
            }
            params.put("merchantId", merchantId);
            params.put("mallId", mallId);
            params.put("channelId", channelId);

            fields.append("case when COUNT(mov.`customer_id`) is null then 0 else  COUNT(mov.`customer_id`) end ");

        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    private void buildMakertIdSql(MerchantOrderSourceType orderSourceType, StringBuffer sql) {
        if (orderSourceType != null && orderSourceType != MerchantOrderSourceType.ALL) {
            if (orderSourceType == MerchantOrderSourceType.SELF) {
                sql.append(" AND p.market_id is null");
            }
            if (orderSourceType == MerchantOrderSourceType.MARKET) {
                sql.append(" AND p.market_id is not null");
            }
        }
    }

    /**
     * 统计订单总量
     *
     * @param mallId
     * @param merchantId
     * @param type
     * @param typeId
     * @param orderSourceType
     * @param startDate
     * @param endDate         @return
     */
    @Override
    public Long countMerchantOrderByMallIdAndMerchantId(Long mallId, Long merchantId, String type, Long typeId, MerchantOrderSourceType orderSourceType, String startDate, String endDate) {

        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from merchant_order mo  ");
            sql.append(" left join merchant_order_item moi on mo.id=moi.merchant_order_id");
            sql.append(" LEFT JOIN product p ON p.`id`=moi.product_id");
            sql.append(" LEFT JOIN channel c ON c.`id`=moi.channel_id");
            sql.append("  where mo.merchant_id=:merchantId and mo.mall_id=:mallId");

            params.put("merchantId", merchantId);
            params.put("mallId", mallId);

            if (type != null && ProductType.valueOf(type) != ProductType.ALL) {
                sql.append(" AND  p.type=:type");
                params.put("type", type);
            }

            if (typeId != null) {
                sql.append(" AND  c.channel_type_id=:typeId");
                params.put("typeId", typeId);
            }


            if (orderSourceType != null && orderSourceType != MerchantOrderSourceType.ALL) {
                if (orderSourceType == MerchantOrderSourceType.SELF) {
                    sql.append(" AND p.market_id is null");
                }
                if (orderSourceType == MerchantOrderSourceType.MARKET) {
                    sql.append(" AND p.market_id is not null");
                }
            }
            if (startDate != null) {
                sql.append(" AND mo.`date_created` >=:startDate");
                params.put("startDate", startDate);
            }
            if (endDate != null) {
                sql.append(" AND mo.`date_created` <=:endDate");
                params.put("endDate", endDate);
            }

            fields.append("COUNT(mo.id)");
        };


        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public PageResult<MerchantOrderDto> findByCustomerIdAndStatusNotIn(Long customerId, List<String> orderStatusList, PageResult pageResult) {
        SQLPageDoing<MerchantOrderDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from merchant_order mo left join (SELECT merchant_order_id,share_fee FROM merchant_order_item)moi ON mo.id = moi.merchant_order_id" +
                    " where 1=1 ");
            sql.append(" and mo.customer_id = :customerId and mo.merchant_role <> 'SUPPLIER' and mo.gift_packet_id is null and mo.is_delete = 'NO' ");
            if (orderStatusList != null && orderStatusList.size() > 0) {
                sql.append(" and mo.status not in(:status) ");
            }
            sql.append(" order by mo.date_created desc ");
            fields.append("DISTINCT mo.*");
            params.put("customerId", customerId);
            params.put("status", orderStatusList);
        };
        return doing.go(pageResult, MerchantOrderDto.class, objectMapper, jdbcTemplate, log);
    }


    @Override
    public PageResult<MerchantOrderDto> findByCustomerIdPage(Long customerId, String orderType, String status, String evaluateStatus, Whether isDelete, PageResult pageResult) {
        SQLPageDoing doing = (sql, fields, params, typeReferenceMap) -> {
            // 拆单
            sql.append("select %s from merchant_order me  ");
            fields.append("me.*");
            if (OrderType.ALL == OrderType.valueOf(orderType)) {
                //已完成（status = FINISHED,evaluateStatus = HAD_EVALUATE\WAIT_EVALUATE\DEFAULT） /待评价（status = FINISHED,evaluateStatus = WAIT_EVALUATE）,不分订单类型
                //评价状态

                if (OrderStatus.FINISHED == OrderStatus.valueOf(status)) {
                    sql.append(" where me.status = 'FINISHED' ");
                    sql.append(" and (me.evaluate_status = 'HAD_EVALUATE' or me.evaluate_status = 'WAIT_EVALUATE') ");
                } else if (OrderStatus.ALL == OrderStatus.valueOf(status) && EvaluateStatus.DEFAULT == EvaluateStatus.valueOf(evaluateStatus)) {
                    sql.append(" where 1=1  ");
                }else if (EvaluateStatus.WAIT_EVALUATE == EvaluateStatus.valueOf(evaluateStatus)) {
                    sql.append(" where me.status = 'FINISHED' ");
                    sql.append(" and me.evaluate_status = 'WAIT_EVALUATE' ");
                }
            } else if (OrderType.GOODS == OrderType.valueOf(orderType)) {
                //GOODS(待发货\待备货（status = WAIT_SHIPPED\WAIT_STOCK,evaluateStatus = DEFAULT）/待收货（status = SHIPPED,evaluateStatus = DEFAULT）/待自提（status = WAIT_SHOP_PICKUP,evaluateStatus = DEFAULT）)
                sql.append(" where me.order_type = 'GOODS' ");
                if (OrderStatus.WAIT_SHIPPED == OrderStatus.valueOf(status)) {
                    sql.append(" and (me.status = 'WAIT_SHIPPED' or me.status = 'WAIT_STOCK') ");
                } else {
                    sql.append(" and me.status = :status ");
                }
                sql.append(" and me.evaluate_status = 'DEFAULT' ");
            } else if (OrderType.SERVICE == OrderType.valueOf(orderType)) {
                //SERVICE
                sql.append(" left join merchant_order_item moi on me.id = moi.merchant_order_id ");
                if ("OVERDUE".equals(status)) {
                    //已过期（status = FINISHED\CANCEL,evaluateStatus = DEFAULT,可用时间小于当前时间）
                    sql.append("where me.order_type = 'SERVICE' and (me.status = 'FINISHED' or me.status = 'CANCEL') and moi.product_date_disabled < now() ");
                } else if (OrderStatus.WAIT_USE == OrderStatus.valueOf(status)) {
                    //待使用(全部未使用、部分未使用,status = WAIT_USE\PART_USE,evaluateStatus = DEFAULT，可用时间大于当前时间)
                    sql.append(" where me.order_type = 'SERVICE' and (me.status = 'WAIT_USE' or me.status = 'PART_USE') and moi.product_date_disabled > now() ");
                }
            }
            //只显示分销订单和自营订单（NO 供货商订单）
            sql.append(" and me.customer_id = :customerId and me.merchant_role <> 'SUPPLIER' and me.is_delete = :isDelete order by me.last_updated desc ");
            params.put("status", status);
            params.put("evaluateStatus", evaluateStatus);
            params.put("customerId", customerId);
            params.put("isDelete", isDelete.toString());
        };
        return doing.go(pageResult, MerchantOrderDto.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public Integer countWaitUseAndPartUse(Long customerId) {
        SQLObjectDoing<Integer> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT COUNT(1) FROM merchant_order me   LEFT JOIN merchant_order_item moi ON me.id = moi.merchant_order_id " +
                    "WHERE me.order_type = 'SERVICE' AND (me.status = 'WAIT_USE' OR me.status = 'PART_USE') AND moi.product_date_disabled > NOW()  " +
                    "AND me.customer_id = :customerId AND me.merchant_role <> 'SUPPLIER' AND me.is_delete = 'NO' ");
            params.put("customerId", customerId);
        };
        return doing.go(Integer.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * @param mallId
     * @param merchantId
     * @param status
     * @return
     */
    @Override
    public Long getOrderStatisticsYesterDay(Long mallId, Long merchantId, OrderStatus status, EvaluateStatus evaluateStatus) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s  FROM merchant_order mo WHERE mo.`mall_id`=:mallId AND mo.`merchant_id`=:merchantId AND mo.`is_delete`='NO'");

            if (status != null && status != OrderStatus.ALL) {
                sql.append(" and mo.status=:status");
                params.put("status", status.toString());
            }
            if (evaluateStatus != null) {
                sql.append(" and mo.evaluate_status=:evaluateStatus");
                params.put("evaluateStatus", evaluateStatus.toString());
            }
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            fields.append("COUNT(mo.`id`)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }


    /**
     * @param mallId
     * @param merchantId
     * @return
     */
    @Override
    public Long getOrderCustomerStatisticsYesterDay(Long mallId, Long merchantId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            /**
             * select  count(c.id) from customer c
             left join merchant_order pe on c.id=pe.customer_id
             */

            sql.append("select %s from order_event oe left join merchant_order mo on mo.order_num = oe.merchant_order_num");
            sql.append(" WHERE oe.`mall_id`=:mallId AND oe.`merchant_id`=:merchantId");
            sql.append(" AND oe.`is_delete`='NO'");
            sql.append(" AND DATE(oe.date_created)=CURDATE()-1");
            sql.append(" AND oe.event_type='ORDERED'");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            fields.append("count(distinct mo.customer_id)");

        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * @param mallId
     * @param merchantId
     * @return
     */
    @Override
    public Long getOrderProductStatisticsYesterDay(Long mallId, Long merchantId) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from merchant_order_item pe");
            sql.append(" where pe.is_delete='NO'");
            sql.append(" and pe.mall_id=:mallId");
            sql.append(" and pe.merchant_id=:merchantId");
            sql.append(" and DATE(pe.date_created)=CURDATE()-1");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            fields.append("sum(pe.buy_count)");
        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * @param mallId
     * @param merchantId
     * @return
     */
    @Override
    public Double getOrderTotalSumStatisticsYesterDay(Long mallId, Long merchantId) {
        SQLObjectDoing<Double> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select  %s from merchant_order mo");
            sql.append(" where mo.is_delete='NO'");
            sql.append(" and mo.mall_id=:mallId");
            sql.append(" and mo.merchant_id=:merchantId");
            sql.append(" and DATE(mo.date_created)=CURDATE()-1");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            fields.append("sum(mo.total_amount)");
        };

        return doing.go(Double.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * @param mallId
     * @param merchantId
     * @param status
     * @return
     */
    @Override
    public Long getPayOrderCustomerCountStatisticsYesterDay(Long mallId, Long merchantId, OrderEventType status) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from order_event oe left join merchant_order mo on oe.merchant_order_num=mo.order_num");
            sql.append(" where oe.is_delete='NO'");
            sql.append(" and oe.mall_id=:mallId");
            sql.append(" and oe.merchant_id=:merchantId");
            sql.append(" and oe.event_type=:status");
            sql.append(" AND DATE(oe.date_created)=CURDATE()-1");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("status", status.toString());

            fields.append("count(distinct mo.customer_id)");
        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 已支付产品数
     *
     * @param mallId
     * @param merchantId
     * @param status
     * @return
     */
    @Override
    public Long getPayProductCountStatisticsYesterDay(Long mallId, Long merchantId, OrderEventType status) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {

            sql.append("select %s from order_event oe left join merchant_order_item moi on oe.merchant_order_num=moi.merchant_order_num");
            sql.append(" where oe.is_delete='NO'");
            sql.append(" and oe.mall_id=:mallId");
            sql.append(" and oe.merchant_id=:merchantId");
            sql.append(" and oe.event_type=:status");
            sql.append(" and DATE(oe.date_created)=CURDATE()-1");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("status", status.toString());

            fields.append("sum(moi.buy_count)");
        };

        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 支付金额
     *
     * @param mallId
     * @param merchantId
     * @param status
     * @return
     */
    @Override
    public Double getPayOrderTotalStatisticsYesterDay(Long mallId, Long merchantId, OrderEventType status) {
        SQLObjectDoing<Double> doing = (sql, fields, params, typeReferenceMap) -> {
            /**
             * select sum(mo.total_amount) from merchant_order mo
             where mo.is_delete='NO'
             and mo.mall_id=1
             and mo.merchant_id=1
             and mo.status='PAYED'
             */
            sql.append("select %s from order_event oe left join merchant_order mo on oe.merchant_order_num=mo.order_num");
            sql.append(" where oe.is_delete='NO'");
            sql.append(" and oe.event_type=:status");
            sql.append(" and oe.mall_id=:mallId");
            sql.append(" and oe.merchant_id=:merchantId");
            sql.append(" and DATE(oe.date_created)=CURDATE()-1");
            params.put("status", status.toString());
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            fields.append("sum(mo.discount_amount)");

        };
        return doing.go(Double.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public PageResult<MerchantOrder> findMallOrderPage(Long mallId, MerchantOrderDto dto, PageResult pageResult) {
        SQLPageDoing<MerchantOrder> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from merchant_order mo left join customer c on mo.customer_Id = c.id where 1=1");

            if (MerchantOrderSourceType.ALL != MerchantOrderSourceType.valueOf(dto.getSourceType())) {
                if (MerchantOrderSourceType.B2D == MerchantOrderSourceType.valueOf(dto.getSourceType())) {
                    sql.append(" and mo.merchant_role = 'SUPPLIER'");
                } else if (MerchantOrderSourceType.D2C == MerchantOrderSourceType.valueOf(dto.getSourceType())) {
                    sql.append(" and mo.merchant_role = 'DISTRIBUTOR'");
                } else {
                    sql.append(" and mo.source_type = :sourceType");
                    params.put("sourceType", dto.getSourceType());
                }
            }

            if (OrderType.ALL != OrderType.valueOf(dto.getOrderType())) {
                sql.append(" and mo.order_type = :orderType");
                params.put("orderType", dto.getOrderType());
            }

            if (StringUtils.isNotBlank(dto.getBeginDate()) && StringUtils.isNotBlank(dto.getEndDate())) {
                sql.append(" AND mo.date_created < :endDate AND mo.date_created > :beginDate");
                params.put("beginDate", RDateUtils.beforeDateStr(dto.getBeginDate(), 0));
                params.put("endDate", RDateUtils.afterDateStr(dto.getEndDate(), 1));
            }

            if (StringUtils.isNotBlank(dto.getOrderNum())) {
                sql.append(" AND mo.order_num LIKE :orderNum");
                params.put("orderNum", "%" + dto.getOrderNum() + "%");
            }

            if (StringUtils.isNotBlank(dto.getShoppingManPhone())) {
                sql.append(" AND c.mobile LIKE :mobile");
                params.put("mobile", "%" + dto.getShoppingManPhone() + "%");
            }

            if (StringUtils.isNotBlank(dto.getMerchantName())) {
                sql.append(" AND mo.merchant_name LIKE :merchantName");
                params.put("merchantName", "%" + dto.getMerchantName() + "%");
            }

            if (OrderStatus.ALL != OrderStatus.valueOf(dto.getStatus())) {
                sql.append(" and mo.status = :status");
                params.put("status", dto.getStatus());
            }

            sql.append(" and mo.mall_id = :mallId");
            params.put("mallId", mallId);
            sql.append(" order by mo.date_created desc");
            fields.append("mo.*");
        };
        return doing.go(pageResult, MerchantOrder.class, objectMapper, jdbcTemplate, log);
    }


    @Override
    public List<MerchantOrder> queryAllPayedOrder() {
        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select b.* from mall_order a ,merchant_order b");
            sql.append(" where a.id = b.mall_order_id   and a.gift_id is  null and b.gift_packet_id is null");
            sql.append(" and a.status='PAYED' and b.status='SHIPPED' ");
            sql.append(" and a.status in ('PAYED','PART_FINISHED')  and b.status='SHIPPED' ");
            sql.append(" and HOUR(timediff(now(),b.date_delivered))>=15*24 and b.is_delete ='NO' and a.is_delete ='NO'");
        };
        return doing.go(MerchantOrder.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public List<MerchantOrder> queryAllOrderOfWaitShopPickpup(Long mallOrderId) {
        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from mall_order a ,merchant_order b");
            sql.append(" where a.id = b.mall_order_id   and a.gift_id is  null and b.gift_packet_id is null");
//            sql.append(" and a.status='PAYED' and b.status='WAIT_SHOP_PICKUP' ");
            sql.append(" and a.status in ('PAYED','PART_FINISHED')  and b.status='WAIT_SHOP_PICKUP' and merchant_role <>'DISTRIBUTOR'");
            sql.append(" and HOUR(timediff(now(),b.date_stock))>=15*24  and b.is_delete ='NO' and a.is_delete ='NO'");
            sql.append(" and a.id = :mallOrderId ");
            params.put("mallOrderId", mallOrderId);
            fields.append("b.*");

        };
        return doing.go(MerchantOrder.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public List<MerchantOrder> queryAllOrderOfWaitShipped(Long mallOrderId) {
        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from mall_order a ,merchant_order b");
            sql.append(" where a.id = b.mall_order_id   and a.gift_id is  null and b.gift_packet_id is null");
//            sql.append(" and a.status='PAYED' and b.status='WAIT_SHIPPED' ");
            sql.append(" and a.status in ('PAYED','PART_FINISHED')  and b.status='WAIT_SHIPPED' and merchant_role <>'DISTRIBUTOR'");
            sql.append(" and HOUR(timediff(now(),b.date_payment))>=7*24  and b.is_delete ='NO' and a.is_delete ='NO'");
            sql.append(" and a.id = :mallOrderId ");
            params.put("mallOrderId", mallOrderId);
            fields.append("b.*");

        };
        return doing.go(MerchantOrder.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public List<MerchantOrder> queryAllOrderOfWaitStock(Long mallOrderId) {
        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from mall_order a ,merchant_order b");
            sql.append(" where a.id = b.mall_order_id ");
//            sql.append(" and a.status='PAYED' and b.status='WAIT_STOCK' ");
            sql.append(" and a.status in ('PAYED','PART_FINISHED') and b.status='WAIT_STOCK' and merchant_role <>'DISTRIBUTOR' and a.gift_id is  null and b.gift_packet_id is null  ");
            sql.append(" and HOUR(timediff(now(),b.date_payment))>=7*24   and b.is_delete ='NO' and a.is_delete ='NO'");
            sql.append(" and a.id = :mallOrderId ");
            params.put("mallOrderId", mallOrderId);
            fields.append("b.*");

        };
        return doing.go(MerchantOrder.class, objectMapper, jdbcTemplate, log);
    }

    /**
     * 下单数
     *
     * @param mallId
     * @param merchantId
     * @return
     */
    public Long getOrderNumStatisticsYesterDay(Long mallId, Long merchantId, OrderEventType orderEventType) {
        SQLObjectDoing<Long> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT %s FROM order_event mo");
            sql.append(" WHERE mo.`mall_id`=:mallId AND mo.`merchant_id`=:merchantId");
            sql.append(" AND mo.`is_delete`='NO'");
            sql.append(" AND DATE(mo.date_created)=CURDATE()-1");
            sql.append(" AND mo.event_type=:orderEventType");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("orderEventType", orderEventType.toString());
            fields.append("count(1)");
        };
        return doing.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 所有包括可取消子订单的mallorderId
     *
     * @return
     */
    @Override
    public List<Long> queryAllMallOrderIdsUnPickupAndExpired() {
        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select distinct b.mall_order_id ");
            sql.append(" from mall_order a ,merchant_order b     ");
            sql.append(" where a.id = b.mall_order_id  and a.gift_id is  null and b.gift_packet_id is null");
//            sql.append(" and a.status='PAYED' and b.status='WAIT_SHOP_PICKUP' ");
            sql.append(" and a.status in ('PAYED','PART_FINISHED') and b.status='WAIT_SHOP_PICKUP'  and b.is_delete ='NO' and a.is_delete ='NO'");

            sql.append(" and HOUR(timediff(now(),b.date_stock))>=15*24");

        };
        return doing.go(Long.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public List<Long> queryAllMallOrderIdsWaitShippedAndExpired() {

        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select distinct b.mall_order_id ");
            sql.append(" from mall_order a ,merchant_order b   ");
            sql.append(" where a.id = b.mall_order_id and a.gift_id is  null and b.gift_packet_id is null");
            sql.append(" and a.status in ('PAYED','PART_FINISHED') and b.status='WAIT_SHIPPED'  and b.is_delete ='NO' and a.is_delete ='NO'");
            sql.append(" and HOUR(timediff(now(),b.date_payment))>=7*24");

        };
        return doing.go(Long.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public List<Long> queryAllMallOrderIdsWaitStockAndExpired() {
        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select distinct b.mall_order_id ");
            sql.append(" from mall_order a ,merchant_order b");
            sql.append(" where a.id = b.mall_order_id and a.gift_id is  null and b.gift_packet_id is null  ");
            sql.append(" and a.status in ('PAYED','PART_FINISHED') and b.status='WAIT_STOCK'  and b.is_delete ='NO' and a.is_delete ='NO'");
            sql.append(" and HOUR(timediff(now(),b.date_payment))>=7*24");
        };
        return doing.go(Long.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public List<MerchantOrderDto> findMallBalanceAccountInfo(Long mallId, String mallOrderNum) {
        SQLListDoing<MerchantOrderDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from merchant_order mo where 1=1 ");
            sql.append(" and mo.merchant_role != 'SUPPLIER'");
            sql.append(" and mo.mall_order_num = :mallOrderNum");
            sql.append(" and mo.mall_id = :mallId");
            params.put("mallId", mallId);
            params.put("mallOrderNum", mallOrderNum);
            fields.append("mo.*");
        };
        return doing.go(MerchantOrderDto.class, objectMapper, jdbcTemplate, log);
    }

    /**
     * 查询渠道订单产品信息
     *
     * @param channelProductDto
     * @param pageResult
     * @return
     */
    @Override
    public PageResult<ChannelProductDto> findProductOrdersByProductNameAndChannelIdAndDate(ChannelProductDto channelProductDto, PageResult pageResult) {
        SQLPageDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from merchant_order mo");
            sql.append(" left join merchant_order_item moi on mo.id=moi.merchant_order_id");
            sql.append(" left join product p on p.id=moi.product_id");
            sql.append(" where mo.merchant_id=:merchantId");
            sql.append(" and mo.mall_id=:mallId and mo.is_delete='NO'");
            sql.append(" and moi.channel_id=:channelId");
            if (StringUtils.isNotEmpty(channelProductDto.getProductName())) {
                sql.append(" and p.name like :productName");
                params.put("productName", "%" + channelProductDto.getProductName() + "%");
            }
            if (StringUtils.isNotEmpty(channelProductDto.getDateStart())) {
                sql.append(" AND mo.date_created >= :dateStart");
                params.put("dateStart", channelProductDto.getDateStart());
            }
            if (StringUtils.isNotEmpty(channelProductDto.getDateEnd())) {
                sql.append(" AND mo.date_created <= :dateEnd");
                params.put("dateEnd", channelProductDto.getDateEnd());
            }
            params.put("merchantId", channelProductDto.getMerchantId());
            params.put("mallId", channelProductDto.getMallId());
            params.put("channelId", channelProductDto.getChannelId());

            sql.append(" ORDER BY mo.date_created desc");

            fields.append("mo.order_num orderNum,p.name productName,moi.product_id productId,moi.buy_count saleCount," +
                    "moi.discount_amount paymentAmount,mo.date_created dateCreated,mo.status status," +
                    "moi.date_settled dateSettle,mo.order_type orderType,mo.product_shopping_type productShoppingType," +
                    "mo.source_type sourceType,mo.merchant_role merchantRole");
        };
        return doing.go(pageResult, ChannelProductDto.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public List<MerchantOrder> findByStatusAndEvaluateStatus(OrderStatus orderStatus, EvaluateStatus evaluateStatus) {

        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from merchant_order a where  is_delete='NO' ");
            sql.append(" and a.status=:orderStatus and a.evaluate_status=:evaluateStatus ");
            sql.append(" and HOUR(timediff(now(),a.last_updated))>=15*24");
            params.put("orderStatus", orderStatus.toString());
            params.put("evaluateStatus", evaluateStatus.toString());
            fields.append("a.*");

        };
        return doing.go(MerchantOrder.class, objectMapper, jdbcTemplate, log);
    }

    @Override
    public List<MerchantOrder> queryAllOrderOfWaitUse(Long mallOrderId) {
        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from merchant_order b");
            sql.append(" where a.id = b.mall_order_id  and t.order_type='SERVICE' and a.gift_id is  null and b.gift_packet_id is null and refundable ='YES'");
            sql.append(" and a.status in ('PART_USE','WAIT_USE')   and b.is_delete ='NO' and a.is_delete ='NO'");
            sql.append(" and i.merchant_role in('SELF','DISTRIBUTOR')");
            sql.append(" and date_format(i.product_date_disabled,'%Y-%m-%d %H:%i') <= date_format(NOW(),'%Y-%m-%d %H:%i')");
            sql.append(" and b.mall_order_id = :mallOrderId ");
            params.put("mallOrderId", mallOrderId);
            fields.append("b.*");
        };
        return doing.go(MerchantOrder.class, objectMapper, jdbcTemplate, log);
    }


    @Override
    public  List<T> queryAllOrderOfExpired(Long mallOrderId,T t) {
        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s ");
            sql.append(" from merchant_order b,merchant_order_item i  ");
            sql.append(" where a.id = b.mall_order_id  and t.order_type='SERVICE' and a.gift_id is  null and b.gift_packet_id is null and refundable ='YES'");
            sql.append(" and a.status in ('PART_USE','WAIT_USE')   and b.is_delete ='NO' and a.is_delete ='NO'");
            sql.append(" and i.merchant_role in('SELF','DISTRIBUTOR')");
            sql.append(" and date_format(i.product_date_disabled,'%Y-%m-%d %H:%i') <= date_format(NOW(),'%Y-%m-%d %H:%i')");
            sql.append(" and b.mall_order_id = :mallOrderId ");
            params.put("mallOrderId", mallOrderId);
            fields.append("i.*");
        };
        return doing.go(t.getClass(), objectMapper, jdbcTemplate, log);
    }

    @Override
    public PageResult<MerchantOrderDto> findByCustomerIdAndStatusIn(Long customerId, List<String> orderStatusList, PageResult pageResult) {
        SQLPageDoing<MerchantOrderDto> doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select %s from merchant_order mo left join (SELECT merchant_order_id,share_fee FROM merchant_order_item)moi ON mo.id = moi.merchant_order_id" +
                    " where 1=1 ");
            sql.append(" and mo.customer_id = :customerId and mo.merchant_role <> 'SUPPLIER' and mo.gift_packet_id is null and mo.is_delete = 'NO' ");
            if (orderStatusList != null && orderStatusList.size() > 0) {
                sql.append(" and mo.status  in(:status) ");
            }
            sql.append(" order by mo.date_created desc ");
            fields.append("DISTINCT mo.*");
            params.put("customerId", customerId);
            params.put("status", orderStatusList);
        };
        return doing.go(pageResult, MerchantOrderDto.class, objectMapper, jdbcTemplate, log);
    }


    @Override
    public List<Long> queryAllMallOrderIdsWaitUseAndExpired() {
        SQLListDoing doing = (sql, fields, params, typeReferenceMap) -> {
            sql.append(" select distinct b.mall_order_id ");
            sql.append(" from merchant_order b,merchant_order_item i  ");
            sql.append(" where  b.id = i.merchant_order_id and  b.order_type='SERVICE' and b.gift_packet_id is null and b.refundable ='YES'");
            sql.append(" and b.status in ('PART_USE','WAIT_USE')  and b.is_delete ='NO'");
            sql.append(" and b.merchant_role in('SELF','DISTRIBUTOR')");
            sql.append(" and date_format(i.product_date_disabled,'%Y-%m-%d %H:%i') <= date_format(NOW(),'%Y-%m-%d %H:%i')");
        };
        return doing.go(Long.class, objectMapper, jdbcTemplate, log);
    }
}
